1. pgAdmin4

1.1 開啟pdAdmin4


1.2 輸入伺服器密碼

  • 密碼:password


1.3 點選伺服器

  • 點選左上角的Browser底下的Servers


1.4 輸入資料庫密碼

  • 密碼:password


1.5 進入pgAdmin4主畫面


1.6 開啟指令編輯器

  • 在左側選單點選資料庫CIQ_Target
  • 點選Query tool


1.7 輸入指令

  • Query Editor中輸入以下指令
copy

(select distinct

GVK.gvkey,

RD.ratingsymbol as splticrm,

(date_trunc('MONTH', date(RD.ratingdate)) + INTERVAL '1 MONTH - 1 day')::DATE as datadate,

ELD.datavalue as country

from spratingdata as RD

join spEntityLevelData as ELD on RD.entitySymbolvalue = ELD.entitySymbolvalue and ratingDataItemId = 21

join spratingidentifier RI on RI.symbolvalue = RD.entitysymbolvalue and symboltypeid = 73

join ciqgvkeyiid as GVK on RI.relatedcompanyid = GVK.relatedcompanyid

where ratingdate between '1990-1-1' and '2022-7-31' and ELD.datavalue='USA' and RD.ratingTypeCode='FCLONG'

order by GVK.gvkey ASC, datadate ASC)

to 'D:\ratingData.csv' delimiter ',' csv header encoding 'UTF8'


1.8 指令執行

  • 按下F5


2. RStudio

2.1 開啟RStudio


2.2 開啟新檔案

  • 點選左上角的File>>New File>>R Script


2.3 R Script


2.4 輸入指令

  • 輸入以下指令
# read the rating data
sprating = read.csv('D:/ratingData.csv')
sprating$gvkey = as.character(sprating$gvkey)# change the data type into character from integer
sprating$datadate = as.Date(sprating$datadate)# change the data type into the date from character

# take all of the gvkey
gvkey = unique(sprating$gvkey)

# build a function to transform the data
FillMisMonthData = function(df){
  gvkeyid = df$gvkey[1]
  library(dplyr)
  # construct a matrix with all of the ends of the month between "1990-01-01" and "2022-07-31"
  ts <- seq(as.Date("1990-02-01"),length=391,by="months")-1
  df_time <- data.frame(datadate=ts)
  data_eotm <- suppressMessages(full_join(df_time,df))
  # fill the same data like gvkey and country
  data_eotm$gvkey = gvkeyid
  data_eotm$country = 'USA'
  library(tidyr)
  # fill the rating data with the last seen
  data_eotm = data_eotm %>% fill(splticrm, .direction = 'down')
  # switch the order in a data frame
  data_eotm_fill = select(data_eotm, gvkey, splticrm, datadate, country)
  return(data_eotm_fill)
}

# use the function on all gvkey, and bind together
sprating_fillmismonth = c()
library(progress)
pb <- progress_bar$new(total = length(gvkey))
for(id in gvkey){
  sprating_gvkey = sprating[sprating$gvkey==id,]
  sprating_gvkey = FillMisMonthData(sprating_gvkey)
  sprating_fillmismonth = rbind(sprating_fillmismonth, sprating_gvkey)
  pb$tick()
  Sys.sleep(1/length(gvkey))
}

# date: yyyy-mm-dd to yyyymm, year and month only
sprating_fillmismonth$datadate = format(as.Date(sprating_fillmismonth$datadate, "%Y-%m-%d"), "%Y%m")

# save the file to txt or csv
write.table(sprating_fillmismonth, 'D:/sprating_EndOfTheMonth.txt', na = "", row.names=FALSE, quote = F, sep = "\t")


2.5 全選

  • 方法1:按下滑鼠右鍵,點select all全選
  • 方法2:鍵盤同時按下CtrlA


2.6 執行指令

  • 方法1:按下編輯器右上角的Run
  • 方法2:鍵盤同時按下CtrlEnter


2.6 完成


3. 確認資料

3.1 開啟本機

  • 點選桌面的本機


3.2 開啟D:/


3.3 尋找資料

  • 紅色框框
    • ratingData.csv是pgAdmin4轉出的檔案
    • sprating_EndOfTheMonth.txt是RStudio整理出最終的資料


3.4 確認資料

  • sprating_EndOfTheMonth.txt打開